package leaveSystem.dao.mysql;

import leaveSystem.dao.DAOException;
import leaveSystem.dao.PublicHolidaysDAO;
import leaveSystem.model.PublicHolidays;

import java.sql.*;
import java.sql.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.math.*;

public class PublicHolidaysDAOImpl implements PublicHolidaysDAO {


	    public PublicHolidays createValueObject() {
	          return new PublicHolidays();
	    }
/*
	    public PublicHolidays getObject(Connection conn, int publicHolidaysId) throws DAOException, SQLException {

	          PublicHolidays valueObject = createValueObject();
	          valueObject.setPublicHolidaysId(publicHolidaysId);
	          load(conn, valueObject);
	          return valueObject;
	    }

	    public void load(Connection conn, PublicHolidays valueObject) throws DAOException, SQLException {

	          String sql = "SELECT * FROM public_holidays WHERE (public_holidaysid = ? ) "; 
	          PreparedStatement stmt = null;

	          try {
	               stmt = conn.prepareStatement(sql);
	               stmt.setInt(1, valueObject.getPublicHolidaysId()); 

	               singleQuery(conn, stmt, valueObject);

	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

*/	    
	    
	    
	    public List<PublicHolidays> loadAll(Connection conn) throws SQLException {

	          String sql = "SELECT * FROM leaveapp.public_holidays ORDER BY public_holidaysid ASC ";
	          List<PublicHolidays> searchResults = listQuery(conn, conn.prepareStatement(sql));

	          return searchResults;
	    }
	    public void insertPublicHolidays(Connection conn, PublicHolidays valueObject) throws SQLException {

	          String sql = "";
	          PreparedStatement stmt = null;
	          ResultSet result = null;

	          try {
	               sql = "INSERT INTO leaveapp.public_holidays ( public_holidaysid, name, description, "
	               + "dateFrom, dateTo) VALUES (?, ?, ?, ?, ?) ";
	               stmt = conn.prepareStatement(sql);

	               stmt.setInt(1, valueObject.getPublicHolidaysId()); 
	               stmt.setString(2, valueObject.getName()); 
	               stmt.setString(3, valueObject.getDescription()); 
	               stmt.setDate(4, (Date) valueObject.getDateFrom()); 
	               stmt.setDate(5, (Date) valueObject.getDateTo()); 

	               int rowcount = databaseUpdate(conn, stmt);
	               if (rowcount != 1) {
	                    //System.out.println("PrimaryKey Error when updating DB!");
	                    throw new SQLException("PrimaryKey Error when updating DB!");
	               }

	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }


	    }

	    public void updatePublicHolidays(Connection conn, PublicHolidays valueObject) throws DAOException, SQLException {

	          String sql = "UPDATE leaveapp.public_holidays SET name = ?, description = ?, dateFrom = ?, "
	               + "dateTo = ? WHERE (public_holidaysid = ? ) ";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              stmt.setString(1, valueObject.getName()); 
	              stmt.setString(2, valueObject.getDescription()); 
	              stmt.setDate(3, (Date) valueObject.getDateFrom()); 
	              stmt.setDate(4, (Date) valueObject.getDateTo()); 

	              stmt.setInt(5, valueObject.getPublicHolidaysId()); 

	              int rowcount = databaseUpdate(conn, stmt);
	              if (rowcount == 0) {
	                   //System.out.println("Object could not be saved! (PrimaryKey not found)");
	                   throw new DAOException("Object could not be saved! (PrimaryKey not found)");
	              }
	              if (rowcount > 1) {
	                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
	                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
	              }
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

	    public void deletePublicHolidays(Connection conn, PublicHolidays valueObject) throws DAOException, SQLException {

	          String sql = "DELETE FROM leaveapp.public_holidays WHERE (public_holidaysid = ? ) ";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              stmt.setInt(1, valueObject.getPublicHolidaysId()); 

	              int rowcount = databaseUpdate(conn, stmt);
	              if (rowcount == 0) {
	                   //System.out.println("Object could not be deleted (PrimaryKey not found)");
	                   throw new DAOException("Object could not be deleted! (PrimaryKey not found)");
	              }
	              if (rowcount > 1) {
	                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
	                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
	              }
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

	    public void deleteAllPublicHolidays(Connection conn) throws SQLException {

	          String sql = "DELETE FROM leaveapp.public_holidays";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              int rowcount = databaseUpdate(conn, stmt);
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

	    /*public int countAll(Connection conn) throws SQLException {

	          String sql = "SELECT count(*) FROM public_holidays";
	          PreparedStatement stmt = null;
	          ResultSet result = null;
	          int allRows = 0;

	          try {
	              stmt = conn.prepareStatement(sql);
	              result = stmt.executeQuery();

	              if (result.next())
	                  allRows = result.getInt(1);
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }
	          return allRows;
	    }*/


	    public List<PublicHolidays> findPublicHolidays(Connection conn, PublicHolidays valueObject) throws SQLException {

	          List<PublicHolidays> searchResults;

	          boolean first = true;
	          StringBuffer sql = new StringBuffer("SELECT * FROM leaveapp.public_holidays WHERE 1=1 ");

	          if (valueObject.getPublicHolidaysId() != 0) {
	              if (first) { first = false; }
	              sql.append("AND public_holidaysid = ").append(valueObject.getPublicHolidaysId()).append(" ");
	          }

	          if (valueObject.getName() != null) {
	              if (first) { first = false; }
	              sql.append("AND name LIKE '").append(valueObject.getName()).append("%' ");
	          }

	          if (valueObject.getDescription() != null) {
	              if (first) { first = false; }
	              sql.append("AND description LIKE '").append(valueObject.getDescription()).append("%' ");
	          }

	          if (valueObject.getDateFrom() != null) {
	              if (first) { first = false; }
	              sql.append("AND dateFrom = '").append(valueObject.getDateFrom()).append("' ");
	          }

	          if (valueObject.getDateTo() != null) {
	              if (first) { first = false; }
	              sql.append("AND dateTo = '").append(valueObject.getDateTo()).append("' ");
	          }


	          sql.append("ORDER BY public_holidaysid ASC ");

	          // Prevent accidential full table results.
	          // Use loadAll if all rows must be returned.
	          if (first)
	               searchResults = new ArrayList();
	          else
	               searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));

	          return searchResults;
	    }

	    public String getDaogenVersion() {
	        return " ";
	    }

	    public int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {

	          int result = stmt.executeUpdate();

	          return result;
	    }

	    /*public void singleQuery(Connection conn, PreparedStatement stmt, PublicHolidays valueObject) 
	          throws DAOException, SQLException {

	          ResultSet result = null;

	          try {
	              result = stmt.executeQuery();

	              if (result.next()) {

	                   valueObject.setPublicHolidaysId(result.getInt("public_holidaysid")); 
	                   valueObject.setName(result.getString("name")); 
	                   valueObject.setDescription(result.getString("description")); 
	                   valueObject.setDateFrom(result.getDate("dateFrom")); 
	                   valueObject.setDateTo(result.getDate("dateTo")); 

	              } else {
	                    //System.out.println("PublicHolidays Object Not Found!");
	                    throw new DAOException("PublicHolidays Object Not Found!");
	              }
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }
	    }*/

	    protected List<PublicHolidays> listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

	          ArrayList<PublicHolidays> searchResults = new ArrayList<PublicHolidays>();
	          ResultSet result = null;

	          try {
	              result = stmt.executeQuery();

	              while (result.next()) {
	                   PublicHolidays temp = createValueObject();

	                   temp.setPublicHolidaysId(result.getInt("public_holidaysid")); 
	                   temp.setName(result.getString("name")); 
	                   temp.setDescription(result.getString("description")); 
	                   temp.setDateFrom(result.getDate("dateFrom")); 
	                   temp.setDateTo(result.getDate("dateTo")); 

	                   searchResults.add(temp);
	              }

	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }

	          return (List<PublicHolidays>)searchResults;
	    }
		@Override
		public List<PublicHolidays> findPublicHolidaysByDates(Connection conn,
				String start, String end) throws Exception {
			/*String fmt = "yyyy-MM-dd";
			SimpleDateFormat sdf = new SimpleDateFormat(fmt);
			sdf.applyPattern(fmt);
			sdf.parse(start.toString());
			
	System.out.println(sdf.parse(start.toString()));
	System.out.println(sdf.parse(start.toString()));*/
			ArrayList<PublicHolidays> searchResults = new ArrayList<PublicHolidays>();
			String sql = "SELECT * FROM leaveapp.public_holidays where public_holidays.dateFrom between '"+start+"' AND '"+end+"';";
	System.out.println(sql);
			Statement stmt = conn.createStatement();
			ResultSet result = stmt.executeQuery(sql);
			while (result.next()) {
			    PublicHolidays temp = createValueObject();
			
			    temp.setPublicHolidaysId(result.getInt("public_holidaysid")); 
			    temp.setName(result.getString("name")); 
			    temp.setDescription(result.getString("description")); 
			    temp.setDateFrom(result.getDate("dateFrom")); 
			    temp.setDateTo(result.getDate("dateTo")); 
			
			    searchResults.add(temp);
			}
			return (List<PublicHolidays>)searchResults;
		}
}
